﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using it_security_project.App_Code.BusinessLogicLayer;
using System.Data.SqlClient;
using System.Data;

namespace it_security_project.App_Code.DataAccessLayer
{

    public class MemberDAL : DALBase
    {
        private const string cryptationSharedSecret = "Password";

        public void RegisterMember(Member member)
        {
            using (SqlConnection conn = CreateConnection())
            {
                //try
                //{
                    SqlCommand cmd = new SqlCommand("dbo.usp_RegisterMember", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    string hashedPassword = Cryptation.HashPassword(member.Password);

                    cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 30).Value = member.Username;
                    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = hashedPassword;
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100).Value = member.EmailAdress;
                    cmd.Parameters.Add("@SecurityQuestion", SqlDbType.NVarChar, 100).Value = member.SecurityQuestion;
                    cmd.Parameters.Add("@SecurityQuestionAnswer", SqlDbType.NVarChar, 100).Value = member.SecurityQuestionAnswer;

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    //member.MemberID = (int)cmd.Parameters["@MemberID"].Value;
                //}
                //catch
                //{
                    //throw new Exception("Error occured in the Data Access Layer");
                //}
            }
        }

        public List<Member> AllUsers()
        {
            var member = new List<Member>(100);
            using (SqlConnection conn = CreateConnection())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    conn.Open();

                    using (var reader = cmd.ExecuteReader())
                    {
                        var MemberIDIndex = reader.GetOrdinal("MemberID");
                        var SexIDIndex = reader.GetOrdinal("SexID");
                        
                        var UsernameIndex = reader.GetOrdinal("Username");
                        var PasswordIndex = reader.GetOrdinal("Password");
                        var EmailIndex = reader.GetOrdinal("Email");
                        var SecretQuestionIndex = reader.GetOrdinal("SecretQuestion");
                        var SecretQuestionAnswerIndex = reader.GetOrdinal("SecretQuestionAnswer");

                        while (reader.Read())
                        {
                            member.Add(new Member
                            {
                                MemberID = (int)reader.GetInt64(MemberIDIndex),
                                Username = reader.GetString(UsernameIndex),
                                Password = reader.GetString(PasswordIndex),
                                EmailAdress = reader.GetString(EmailIndex),
                                SecurityQuestionAnswer = reader.GetString(SecretQuestionAnswerIndex),
                                SecurityQuestion = reader.GetString(SecretQuestionIndex)
                            });

                        }
            }
        }
        catch
        {
            throw new ApplicationException("An error has occured in the data access layer.");
        }
        }
        return member;
        }

        public Member GetMemberByUsername(string username)
        {
            using (SqlConnection conn = CreateConnection())
            {
                //try
                //{
                    SqlCommand cmd = new SqlCommand("dbo.usp_GetMemberByUsername", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@Username", username);

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var MemberIDIndex = reader.GetOrdinal("MemberID");
                            var RoleIndex = reader.GetOrdinal("Role");
                            var UsernameIndex = reader.GetOrdinal("Username");
                            var UserPasswordIndex = reader.GetOrdinal("Password");
                            var EmailIndex = reader.GetOrdinal("Email");
                            var SecurityQuestionIndex = reader.GetOrdinal("SecurityQuestion");
                            var SecurityQuestionAnswerIndex = reader.GetOrdinal("SecurityQuestionAnswer");

                            return new Member
                            {
                                MemberID = reader.GetInt32(MemberIDIndex),
                                Role = (Roles) Enum.Parse(typeof(Roles), reader.GetString(RoleIndex), true),
                                Username = reader.GetString(UsernameIndex),
                                Password = reader.GetString(UserPasswordIndex),
                                EmailAdress = reader.GetString(EmailIndex),
                                SecurityQuestion = reader.GetString(SecurityQuestionIndex),
                                SecurityQuestionAnswer = reader.GetString(SecurityQuestionAnswerIndex)
                            };
                        }
                    }

                    return null;
                //}
                //catch
                //{
                //    throw new ApplicationException("An error has occured in the data access layer");
                //}
            }
            }


        public void Ban(string UserIPAddress, long ticks)
        {
            using (SqlConnection conn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand("dbo.usp_AddBan", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@IP", SqlDbType.NVarChar, 50).Value = UserIPAddress;
                cmd.Parameters.Add("@Time", SqlDbType.NVarChar, 50).Value = ticks.ToString();
                conn.Open();

                cmd.ExecuteNonQuery();
            }
        }

            public Member GetUsernameIDOnUser(string username)
            {
            using (SqlConnection conn = CreateConnection())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@Username", username);

                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var MemberIDIndex = reader.GetOrdinal("MemberID");
                            var UsernameIndex = reader.GetOrdinal("MemberID");
                            var RoleIDIndex = reader.GetOrdinal("MemberID");
                            var PasswordIDIndex = reader.GetOrdinal("MemberID");
                            var EmailIndex = reader.GetOrdinal("MemberID");
                            var SecurityQuestionIndex = reader.GetOrdinal("MemberID");
                            var SecurityQuestionAnswerIndex = reader.GetOrdinal("MemberID");
                            
                            return new Member
                            {                            
                                MemberID = (int)reader.GetInt64(MemberIDIndex),
                                Role = (Roles)Enum.Parse(typeof(Roles), reader.GetString(RoleIDIndex), true),
                                Username = reader.GetString(UsernameIndex),
                                Password = reader.GetString(PasswordIDIndex),
                                EmailAdress = reader.GetString(EmailIndex),
                                SecurityQuestion = reader.GetString(SecurityQuestionIndex),
                                SecurityQuestionAnswer = reader.GetString(SecurityQuestionAnswerIndex),

                            };
                        }
                    }

                    return null;
                }
                catch
                {
                    throw new ApplicationException("An error has occured in the data access layer");
                }
            }
        }




            public bool IsBanned(string IP, string currenttime)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand("dbo.usp_GetBannedIP", conn);
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.AddWithValue("@IP", IP);

                        conn.Open();
                        string time = "0";
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            
                            if (reader.Read())
                            {
                                var ipdb = reader.GetOrdinal("Until");
                                time = reader.GetString(ipdb);
                            }
                        }

                        if (long.Parse(currenttime) > long.Parse(time))
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                        
                    }
                    catch
                    {
                        throw new ApplicationException("An error has occured in the data access layer");
                    }
                }
            }

            internal void ChangePassword(string username, string password)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand("dbo.usp_ChangePassword", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    string hashedPassword = Cryptation.HashPassword(password);

                    cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 30).Value = username;
                    cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = hashedPassword;

                    conn.Open();

                    cmd.ExecuteNonQuery();
                }
            }

            internal void ChangeEmail(string username, string email)
            {
                using (SqlConnection conn = CreateConnection())
                {
                    SqlCommand cmd = new SqlCommand("dbo.usp_ChangeEmail", conn);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 30).Value = username;
                    cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100).Value = email;

                    conn.Open();

                    cmd.ExecuteNonQuery();
                }
            }
    }
}